﻿IF OBJECT_ID ('CHO_HOLIDAY_CALENDAR') IS NOT NULL
	DROP TABLE CHO_HOLIDAY_CALENDAR
GO

CREATE TABLE CHO_HOLIDAY_CALENDAR
	(
	CAL_ID			INT NOT NULL,
	CAL_NAME   		NVARCHAR(50) NOT NULL,
	CAL_DESC   		NVARCHAR(2500),
	CREATED_BY		NVARCHAR(100) NOT NULL,
	CREATED_DATE	DATETIME NOT NULL,
	MODIFIED_BY		NVARCHAR(100) NOT NULL,
	MODIFIED_DATE	DATETIME NOT NULL,
	CONSTRAINT PK_CHO_TASK_HOLIDAY_CALENDAR PRIMARY KEY (CAL_ID)
	)
GO

CREATE INDEX IX_CHO_TASK_HOLIDAY_CALENDAR
	ON CHO_HOLIDAY_CALENDAR (CAL_NAME)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_HOLIDAY_CALENDAR_AUDIT') IS NOT NULL
	DROP TABLE CHO_HOLIDAY_CALENDAR_AUDIT
GO

CREATE TABLE CHO_HOLIDAY_CALENDAR_AUDIT
	(
	CAL_ID			INT NOT NULL,
	CAL_NAME   		NVARCHAR(50) NOT NULL,
	CAL_DESC   		NVARCHAR(2500),
	CREATED_BY		NVARCHAR(100) NOT NULL,
	CREATED_DATE	DATETIME NOT NULL,
	MODIFIED_BY		NVARCHAR(100) NOT NULL,
	MODIFIED_DATE	DATETIME NOT NULL,
	CONSTRAINT PK_CHO_TASK_HOLIDAY_CALENDAR_AUDIT PRIMARY KEY (CAL_ID, MODIFIED_DATE)
	)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_HOLIDAY_CALENDAR_DATES') IS NOT NULL
	DROP TABLE CHO_HOLIDAY_CALENDAR_DATES
GO

CREATE TABLE CHO_HOLIDAY_CALENDAR_DATES
	(
	CAL_ID			INT NOT NULL,
	CAL_DATE   		DATETIME NOT NULL,
	CAL_DATE_DESC	NVARCHAR(2500),
	CREATED_BY		NVARCHAR(100) NOT NULL,
	CREATED_DATE	DATETIME NOT NULL,
	MODIFIED_BY		NVARCHAR(100) NOT NULL,
	MODIFIED_DATE	DATETIME NOT NULL,
	CONSTRAINT PK_CHO_TASK_HOLIDAY_CALENDAR_DATES PRIMARY KEY (CAL_ID, CAL_DATE)
	)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_HOLIDAY_CALENDAR_DATES_AUDIT') IS NOT NULL
	DROP TABLE CHO_HOLIDAY_CALENDAR_DATES_AUDIT
GO

CREATE TABLE CHO_HOLIDAY_CALENDAR_DATES_AUDIT
	(
	CAL_ID			INT NOT NULL,
	CAL_DATE   		DATETIME NOT NULL,
	CAL_DATE_DESC	NVARCHAR(2500),
	CREATED_BY		NVARCHAR(100) NOT NULL,
	CREATED_DATE	DATETIME NOT NULL,
	MODIFIED_BY		NVARCHAR(100) NOT NULL,
	MODIFIED_DATE	DATETIME NOT NULL,
	CONSTRAINT PK_CHO_TASK_HOLIDAY_CALENDAR_DATES_AUDIT PRIMARY KEY (CAL_ID, CAL_DATE, MODIFIED_DATE)
	)
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_SELECT_ALL_HOLIDAY_CALENDARS') IS NOT NULL
	DROP PROC CHO_SELECT_ALL_HOLIDAY_CALENDARS
GO

CREATE PROCEDURE CHO_SELECT_ALL_HOLIDAY_CALENDARS
		AS
	
	SET NOCOUNT ON

	SELECT CAL_ID, CAL_NAME, CAL_DESC, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE FROM CHO_HOLIDAY_CALENDAR
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR') IS NOT NULL
	DROP PROC CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR
GO

CREATE PROCEDURE CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR
		@CAL_NAME NVARCHAR(50),
		@CAL_DESC NVARCHAR(2500),
		@MODIFIED_BY NVARCHAR(100),
		@CAL_ID INT OUTPUT
		AS
	
	SET NOCOUNT ON

	SELECT @CAL_ID = NULL

	DECLARE @DATE_UPDATED DATETIME
	DECLARE @ERR_MESSAGE NVARCHAR(255)

	SELECT @CAL_ID = CAL_ID FROM CHO_HOLIDAY_CALENDAR WHERE CAL_NAME = @CAL_NAME
	SELECT @DATE_UPDATED = GETDATE()
	
	IF @CAL_ID IS NULL
	BEGIN
		INSERT CHO_HOLIDAY_CALENDAR (CAL_NAME, CAL_DESC, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
		VALUES (@CAL_NAME, @CAL_DESC, @MODIFIED_BY, @DATE_UPDATED, @MODIFIED_BY, @DATE_UPDATED)
		SELECT @CAL_ID = @@IDENTITY
	END
	ELSE
	BEGIN
		UPDATE CHO_HOLIDAY_CALENDAR
			SET CAL_DESC = @CAL_DESC,
				MODIFIED_BY = @MODIFIED_BY,
				MODIFIED_DATE = @DATE_UPDATED
			WHERE
				CAL_NAME = @CAL_NAME				
	END
		
	INSERT INTO CHO_HOLIDAY_CALENDAR_AUDIT 
		SELECT * FROM CHO_HOLIDAY_CALENDAR
		WHERE
			CAL_NAME = @CAL_NAME		

GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_DELETE_TASK_HOLIDAY_CALENDAR') IS NOT NULL
	DROP PROC CHO_DELETE_TASK_HOLIDAY_CALENDAR
GO

CREATE PROCEDURE CHO_DELETE_TASK_HOLIDAY_CALENDAR
		@CAL_NAME NVARCHAR(50),
		@MODIFIED_BY NVARCHAR(100),
		@CAL_ID INT OUTPUT
		AS

	SET NOCOUNT ON

	SELECT @CAL_ID = NULL

	SELECT @CAL_ID = CAL_ID FROM CHO_HOLIDAY_CALENDAR WHERE CAL_NAME = @CAL_NAME
	IF @CAL_ID is NULL
	BEGIN
		SELECT @CAL_ID = 0
	END
	ELSE
	BEGIN
		UPDATE CHO_HOLIDAY_CALENDAR SET MODIFIED_BY = @MODIFIED_BY, MODIFIED_DATE = GETDATE() WHERE CAL_NAME = @CAL_NAME

		INSERT INTO CHO_HOLIDAY_CALENDAR_AUDIT 
			SELECT * FROM CHO_HOLIDAY_CALENDAR
			WHERE
				CAL_ID = @CAL_ID

		DELETE FROM CHO_HOLIDAY_CALENDAR WHERE CAL_ID = @CAL_ID
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_GET_ALL_HOLIDAY_CALENDAR_DATES_BY_ID') IS NOT NULL
	DROP PROC CHO_GET_ALL_HOLIDAY_CALENDAR_DATES_BY_ID
GO

CREATE PROCEDURE CHO_GET_ALL_HOLIDAY_CALENDAR_DATES_BY_ID
		@CAL_ID INT
		AS
	
	SET NOCOUNT ON

	SELECT CAL_ID, CAL_DATE, CAL_DATE_DESC, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE FROM CHO_HOLIDAY_CALENDAR_DATES WHERE CAL_ID = @CAL_ID
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_GET_ALL_HOLIDAY_CALENDAR_DATES_BY_NAME') IS NOT NULL
	DROP PROC CHO_GET_ALL_HOLIDAY_CALENDAR_DATES_BY_NAME
GO

CREATE PROCEDURE CHO_GET_ALL_HOLIDAY_CALENDAR_DATES_BY_NAME
		@CAL_NAME NVARCHAR(50)
		AS
	
	SET NOCOUNT ON

	SELECT 
		T1.CAL_ID, 
		T1.CAL_DATE, 
		T1.CAL_DATE_DESC,
		T1.CREATED_BY,
		T1.CREATED_DATE,
		T1.MODIFIED_BY,
		T1.MODIFIED_DATE
	FROM 
		CHO_HOLIDAY_CALENDAR_DATES T1, 
		CHO_HOLIDAY_CALENDAR T2 
	WHERE 
		T2.CAL_NAME = @CAL_NAME
		AND T1.CAL_ID = T2.CAL_ID
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR_DATES') IS NOT NULL
	DROP PROC CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR_DATES
GO

CREATE PROCEDURE CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR_DATES
		@CAL_ID INT,
		@CAL_DATE DATETIME,
		@CAL_DATE_DESC NVARCHAR(2500),
		@MODIFIED_BY NVARCHAR(100)
		AS
	
	SET NOCOUNT ON

	DECLARE @DATE_UPDATED DATETIME
	DECLARE @ERR_MESSAGE NVARCHAR(255)

	SELECT @DATE_UPDATED = GETDATE()
	
	IF NOT EXISTS(SELECT 1 FROM CHO_HOLIDAY_CALENDAR_DATES WHERE CAL_ID = @CAL_ID AND CAL_DATE = @CAL_DATE)
	BEGIN
		INSERT CHO_HOLIDAY_CALENDAR_DATES (CAL_ID, CAL_DATE, CAL_DATE_DESC, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)
			VALUES (@CAL_ID, @CAL_DATE, @CAL_DATE_DESC, @MODIFIED_BY, @DATE_UPDATED, @MODIFIED_BY, @DATE_UPDATED)
	END
	ELSE
	BEGIN
		UPDATE CHO_HOLIDAY_CALENDAR_DATES
			SET 
				CAL_DATE_DESC = @CAL_DATE_DESC,
				MODIFIED_BY = @MODIFIED_BY,
				MODIFIED_DATE = @DATE_UPDATED
			WHERE
				CAL_ID = @CAL_ID AND CAL_DATE = @CAL_DATE		
	END
		
	INSERT INTO CHO_HOLIDAY_CALENDAR_DATES_AUDIT 
		SELECT * FROM CHO_HOLIDAY_CALENDAR_DATES
		WHERE
			CAL_ID = @CAL_ID AND CAL_DATE = @CAL_DATE
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_DELETE_TASK_HOLIDAY_CALENDAR_DATE') IS NOT NULL
	DROP PROC CHO_DELETE_TASK_HOLIDAY_CALENDAR_DATE
GO

CREATE PROCEDURE CHO_DELETE_TASK_HOLIDAY_CALENDAR_DATE
		@CAL_ID INT,
		@CAL_DATE DATETIME,
		@MODIFIED_BY NVARCHAR(100)
		AS

	SET NOCOUNT ON

	IF EXISTS(SELECT 1 FROM CHO_HOLIDAY_CALENDAR_DATES WHERE CAL_ID = @CAL_ID AND CAL_DATE = @CAL_DATE)
	BEGIN
		UPDATE CHO_HOLIDAY_CALENDAR_DATES SET MODIFIED_BY = @MODIFIED_BY, MODIFIED_DATE = GETDATE() WHERE CAL_ID = @CAL_ID AND CAL_DATE = @CAL_DATE

		INSERT INTO CHO_HOLIDAY_CALENDAR_DATES_AUDIT 
			SELECT * FROM CHO_HOLIDAY_CALENDAR_DATES
			WHERE
				CAL_ID = @CAL_ID AND CAL_DATE = @CAL_DATE

		DELETE FROM CHO_HOLIDAY_CALENDAR_DATES WHERE CAL_ID = @CAL_ID AND CAL_DATE = @CAL_DATE
	END
GO

----------------------------------------------------------------

IF OBJECT_ID ('CHO_CLONE_HOLIDAY_CALENDAR') IS NOT NULL
	DROP PROC CHO_CLONE_HOLIDAY_CALENDAR
GO

CREATE PROCEDURE CHO_CLONE_HOLIDAY_CALENDAR
		@CAL_NAME NVARCHAR(50),
		@NEW_CAL_NAME NVARCHAR(50),
		@MODIFIED_BY NVARCHAR(100),
		@CAL_ID INT OUTPUT
		AS

	SET NOCOUNT ON

	DECLARE @NEW_CAL_ID INT

	SELECT @CAL_ID = NULL

	SELECT @CAL_ID = CAL_ID FROM CHO_HOLIDAY_CALENDAR WHERE CAL_NAME = @CAL_NAME
	IF @CAL_ID is NULL
	BEGIN
		SELECT @CAL_ID = 0
	END
	ELSE
	BEGIN
		EXEC CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR @NEW_CAL_NAME, NULL, @MODIFIED_BY, @NEW_CAL_ID OUT

		DECLARE @CAL_DATE DATETIME
		DECLARE @CAL_DATE_DESC NVARCHAR(2500)

		DECLARE HOLIDAY_CALENDAR_DATES_CURSOR CURSOR FOR
		SELECT
			CAL_DATE,
			CAL_DATE_DESC
		FROM CHO_HOLIDAY_CALENDAR_DATES WHERE CAL_ID = @CAL_ID;

		OPEN HOLIDAY_CALENDAR_DATES_CURSOR;
		FETCH NEXT FROM HOLIDAY_CALENDAR_DATES_CURSOR
		INTO
			@CAL_DATE,
			@CAL_DATE_DESC
		;
		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR_DATES 
				@NEW_CAL_ID,
				@CAL_DATE,
				@CAL_DATE_DESC,
				@MODIFIED_BY

			FETCH NEXT FROM HOLIDAY_CALENDAR_DATES_CURSOR
			INTO
				@CAL_DATE,
				@CAL_DATE_DESC
			;
		END;
		CLOSE HOLIDAY_CALENDAR_DATES_CURSOR;
		DEALLOCATE HOLIDAY_CALENDAR_DATES_CURSOR;
	END
GO

-------------------------------------------------------------------------------------

DECLARE @CAL_ID INT
EXECUTE CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR '<NULL>', 'Empty Calendar', 'XBBKP28', @CAL_ID OUT
SELECT "@CAL_ID" = @CAL_ID
GO

DECLARE @CAL_ID INT
EXECUTE CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR 'GLOBAL-US', 'Global US Holidays', 'XBBKP28', @CAL_ID OUT
SELECT "@CAL_ID" = @CAL_ID
GO

EXECUTE CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR_DATES 1, '06/05/2013', 'Memorial Day', 'XBBKP28'
GO

DECLARE @CAL_ID INT
EXECUTE CHO_ADD_OR_UPDATE_TASK_HOLIDAY_CALENDAR 'CHUBB', 'CHUBB Holidays', 'XBBKP28', @CAL_ID OUT
SELECT "@CAL_ID" = @CAL_ID
GO
